﻿/*
'Copyright ?1995-2007, Camstar Systems, Inc. All Rights Reserved.
'Description:批次类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2020-4-20
*/
using CamstarAPI;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using uMES.LeanManufacturing.DBUtility;
using uMES.LeanManufacturing.ParameterDTO;

namespace uMES.LeanManufacturing.ReportBusiness
{
  public   class uMESContainerBusiness
    {
        /// <summary>
        /// 获取表信息,简单查询
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="conditionFiled"></param>
        /// <param name="conditionFiledValue"></param>
        /// <returns></returns>
        public DataTable GetTableInfo(string tableName, string conditionFiled, object conditionFiledValue)
        {

            string strSql = " select *  from " + tableName;
            if (!string.IsNullOrWhiteSpace(conditionFiled))
            {
                if (conditionFiledValue.GetType().ToString() == "System.DateTime")
                {
                    strSql += " where =to_date('" + DateTime.Parse(conditionFiledValue.ToString()).ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy/MM/dd hh24:mi:ss')";
                }
                else
                {
                    strSql += " where " + conditionFiled + "='" + conditionFiledValue.ToString() + "'";
                }
            }

            return OracleHelper.Query(strSql).Tables[0];
        }

        /// <summary>
        /// 更新表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="updatePara"></param>
        /// <param name="conditionPara"></param>
        /// <returns></returns>
        public bool UpdateTableByField(string tableName , Dictionary<string, Object>  updatePara, Dictionary<string, Object> conditionPara) {
            bool result = false;

            if (updatePara.Keys.Count == 0)
            {
                return result;
            }
            if (conditionPara.Keys.Count == 0)
            {
                return result;
            }
            var strSql  = "UPDATE " + tableName + " SET ";
            var n = 0;
            foreach(string updateKey  in updatePara.Keys){
                if (n > 0)
                {
                    strSql += ",";
                }

                if (updatePara[updateKey].GetType().ToString() == "System.DateTime")
                {
                    strSql += updateKey + "=to_date('" + DateTime.Parse(updatePara[updateKey].ToString()).ToString("yyyy/MM/dd") + "','yyyy/MM/dd')";
                }
                else
                {
                    if (string.IsNullOrWhiteSpace(updatePara[updateKey].ToString()))
                    {
                        strSql += updateKey + "=" + "''";
                    }
                    else
                    {
                        strSql += updateKey + "=" + updatePara[updateKey] + "";
                    }
                }
                n++;
            }
            n = 0;
            foreach (string conditionKey in conditionPara.Keys)
            {
                if (n == 0)
                {
                    strSql += " where ";
                }
                else {
                    strSql += " and ";
                }

                if (string.IsNullOrWhiteSpace(conditionPara[conditionKey].ToString()) || conditionPara[conditionKey].ToString() == "''")
                {
                    strSql += conditionKey + " is null ";
                }
                else {
                    strSql += conditionKey + "=" + conditionPara[conditionKey];
                }
                n++;
            }

            if (OracleHelper.ExecuteSql(strSql)>0)
                result =true;

            return result;
        }

        /// <summary>
        /// 获取订单所有已开卡数量
        /// </summary>
        /// <param name="mfgorderID"></param>
        /// <returns></returns>
        public int GetAllStartQty(string mfgorderID)
        {
            string strSql = @"select nvl(sum(c.qty),0) sumQty from container c 
            where c.parentcontainerid is null and c.status<>0";
            strSql += $" and c.mfgorderid='{mfgorderID}'";

            int re = Convert.ToInt32(OracleHelper.GetSingle(strSql));
            return re;

        }

        /// <summary>
        /// 获取批次下一个批次号
        /// </summary>
        /// <param name="strPrefix">产品+年份（2位）eg:T001/20</param>
        /// <returns></returns>
        public int GetNewContainerNextNo(string strPrefix)
        {
            int re = 0;

            string strSql = @"select c.containername from container c where c.parentcontainerid is null and c.status<>0 ";

            strSql += $" and c.containername like '{strPrefix}%'";

            strSql += " order by c.containerid desc";

            DataTable dt = OracleHelper.Query(strSql).Tables[0];

            int tempNo = 0; string tempContainerName = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tempContainerName = dt.Rows[i]["ContainerName"].ToString();
                if (int.TryParse(tempContainerName.Replace(strPrefix, ""), out tempNo))
                {
                    if (tempNo > re)
                        re = tempNo;
                    else
                        break;
                }
            }
            return ++re;
        }

        /// <summary>
        /// 获取子批次下一个批次号
        /// </summary>
        /// <param name="strPrefix">产品+大批次号 eg:T001/2001</param>
        /// <returns></returns>
        public int GetNewChildContainerNextNo(string strPrefix)
        {
            int re = 0;

            string strSql = @"select c.containername from container c where c.parentcontainerid is not null and c.status<>0 ";

            strSql += $" and c.containername like '{strPrefix}%'";

            strSql += " order by c.containerid desc";

            DataTable dt = OracleHelper.Query(strSql).Tables[0];

            int tempNo = 0; string tempContainerName = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tempContainerName = dt.Rows[i]["ContainerName"].ToString();
                if (int.TryParse(tempContainerName.Replace(strPrefix, ""), out tempNo))
                {
                    if (tempNo > re)
                        re = tempNo;
                    else
                        break;
                }
            }
            return ++re;
        }

        /// <summary>
        /// 批次创建
        /// </summary>
        /// <param name="para"></param>
        /// <param name="apiUserNmae"></param>
        /// <param name="apiPassword"></param>
        /// <returns></returns>
        public ResultModel StartContainer(ContainerStartModel para, string apiUserNmae, string apiPassword)
        {
            ResultModel re = new ResultModel(false, "");

            //创建批次
            var m_DataList = new List<ClientAPIEntity>();
            var dataEntity = new ClientAPIEntity();
            dataEntity = new ClientAPIEntity("ContainerName", InputTypeEnum.Details, DataTypeEnum.DataField, para.ContainerName, "");
            m_DataList.Add(dataEntity);

            dataEntity = new ClientAPIEntity("Level", InputTypeEnum.Details, DataTypeEnum.NamedObjectField, para.Level, "");
            m_DataList.Add(dataEntity);

            dataEntity = new ClientAPIEntity("Product", InputTypeEnum.Details, DataTypeEnum.RevisionedObjectField, para.ProductName, para.ProductRev);
            if (string.IsNullOrWhiteSpace(para.ProductRev))
                dataEntity.ClientDataIsROR = true;
            m_DataList.Add(dataEntity);

            dataEntity = new ClientAPIEntity("Owner", InputTypeEnum.Details, DataTypeEnum.NamedObjectField, para.Owner, "");
            m_DataList.Add(dataEntity);

            dataEntity = new ClientAPIEntity("Qty", InputTypeEnum.Details, DataTypeEnum.DataField, para.Qty, "");
            m_DataList.Add(dataEntity);

            if (!string.IsNullOrWhiteSpace(para.Uom))
            {
                dataEntity = new ClientAPIEntity("UOM", InputTypeEnum.Details, DataTypeEnum.NamedObjectField, para.Uom, "");
                m_DataList.Add(dataEntity);
            }
            dataEntity = new ClientAPIEntity("StartReason", InputTypeEnum.Details, DataTypeEnum.NamedObjectField, para.StartReason, "");
            m_DataList.Add(dataEntity);

            dataEntity = new ClientAPIEntity("Workflow", InputTypeEnum.CurrentStatusDetails, DataTypeEnum.RevisionedObjectField, para.WorkflowName, para.WorkflowRev);
            if (string.IsNullOrWhiteSpace(para.WorkflowRev))
                dataEntity.ClientDataIsROR = true;
            m_DataList.Add(dataEntity);

            dataEntity = new ClientAPIEntity("MfgOrder", InputTypeEnum.Details, DataTypeEnum.NamedObjectField, para.Mfgorder, "");
            m_DataList.Add(dataEntity);

            if (!string.IsNullOrWhiteSpace(para.Priority))
            {
                dataEntity = new ClientAPIEntity("PriorityCode", InputTypeEnum.Details, DataTypeEnum.NamedObjectField, para.Priority, "");
                m_DataList.Add(dataEntity);
            }

            dataEntity = new ClientAPIEntity("PlannedStartDate", InputTypeEnum.Details, DataTypeEnum.DataField, para.PlannedStartDate, "");
            m_DataList.Add(dataEntity);


            dataEntity = new ClientAPIEntity("PlannedCompletionDate", InputTypeEnum.Details, DataTypeEnum.DataField, para.PlannedCompletionDate, "");
            m_DataList.Add(dataEntity);

            dataEntity = new ClientAPIEntity("StartEmployee", InputTypeEnum.Details, DataTypeEnum.NamedObjectField, para.StartEmployee, "");
            m_DataList.Add(dataEntity);

            dataEntity = new ClientAPIEntity("ContainerComment", InputTypeEnum.Details, DataTypeEnum.DataField, para.ContainerComment, "");
            m_DataList.Add(dataEntity);

            //子批次
            var m_ChildList = new Dictionary<string, List<ClientAPIEntity>>();
            var dtChildList = para.ChildList;

            if (dtChildList != null)
            {
                foreach (DataRow dr in dtChildList.Rows)
                {
                    var m_Child = new List<ClientAPIEntity>();
                    var childEntity = new ClientAPIEntity();

                    childEntity.ClientDataTypeEnum = DataTypeEnum.DataField;
                    childEntity.ClientDataName = "ContainerName";
                    childEntity.ClientDataValue = dr["ContainerName"].ToString();
                    m_Child.Add(childEntity);

                    childEntity = new ClientAPIEntity();
                    childEntity.ClientDataTypeEnum = DataTypeEnum.NamedObjectField;
                    childEntity.ClientDataName = "Level";
                    childEntity.ClientDataValue = para.Level;
                    m_Child.Add(childEntity);

                    childEntity = new ClientAPIEntity();
                    childEntity.ClientDataTypeEnum = DataTypeEnum.DataField;
                    childEntity.ClientDataName = "Qty";
                    childEntity.ClientDataValue = dr["Qty"].ToString();
                    m_Child.Add(childEntity);

                    m_ChildList.Add(dtChildList.Rows.IndexOf(dr).ToString(), m_Child);

                }
            }

            //批次属性
            var m_AttributeList = new Dictionary<string, List<ClientAPIEntity>>();
            var attributeDT = para.AttributeList;

            if (attributeDT != null)
            {
                for (int i = 0; i < attributeDT.Rows.Count; i++)
                {
                    var m_AttrChild = new List<ClientAPIEntity>();
                    var attEntity = new ClientAPIEntity();

                    attEntity.ClientDataTypeEnum = DataTypeEnum.DataField;
                    attEntity.ClientDataName = "Name";
                    attEntity.ClientDataValue = attributeDT.Rows[i]["Name"];
                    m_AttrChild.Add(attEntity);

                    attEntity = new ClientAPIEntity();
                    attEntity.ClientDataTypeEnum = DataTypeEnum.DataField;
                    attEntity.ClientDataName = "DataType";
                    attEntity.ClientDataValue = "4";
                    m_AttrChild.Add(attEntity);

                    attEntity = new ClientAPIEntity();
                    attEntity.ClientDataTypeEnum = DataTypeEnum.DataField;
                    attEntity.ClientDataName = "AttributeValue";
                    attEntity.ClientDataValue = attributeDT.Rows[i]["Value"];
                    m_AttrChild.Add(attEntity);

                    m_AttributeList.Add(i.ToString().Trim(), m_AttrChild);
                }
            }
            string strInfo = "";

            var api = new CamstarClientAPI(apiUserNmae, apiPassword);

            re.IsSuccess = api.StartWithAttributes(m_DataList, m_ChildList, m_AttributeList, ref strInfo);
            re.Message = strInfo;
            return re;

        }

        #region 批量开卡

        /// <summary>
        /// 批量数据处理
        /// </summary>
        /// <param name="dt"></param>
        public void dealPopupDt(ref DataTable dt)
        {
            dt.PrimaryKey = null;
            if (dt.Columns.Contains("_id_column"))
                dt.Columns.Remove("_id_column");
            //dt处理
            if (!dt.Columns.Contains("mfgQty"))
                dt.Columns.Add("mfgQty");
            if (!dt.Columns.Contains("Product"))
                dt.Columns.Add("Product");
            if (!dt.Columns.Contains("Workflow"))
                dt.Columns.Add("Workflow");
            if (!dt.Columns.Contains("ContainerNo"))
                dt.Columns.Add("ContainerNo");

            //循环处理，计算每列的可投数量
            Dictionary<string, int> productNo = new Dictionary<string, int>();//存储已经计算过的件号和批次序号
            foreach (DataRow dr in dt.Rows)
            {
                
                int mfgQty = int.Parse(dr["Qty"].ToString());
                int allStartedQty = GetAllStartQty(dr["mfgorderid"].ToString());
                int canStartQty = mfgQty - allStartedQty;

                dr["mfgQty"] = dr["Qty"];
                dr["Qty"] = canStartQty.ToString();

                dr["Product"] = dr["Productname"].ToString() + ":" + dr["productrev"].ToString();

                if (!string.IsNullOrWhiteSpace(dr["WorkflowName"].ToString()))
                {
                    dr["Workflow"] = dr["WorkflowName"].ToString() + ":" + dr["Workflowrev"].ToString();
                }
                else { dr["Workflow"] = ""; }

                //批次号
                int nexNo = 0; string year = DateTime.Now.Year.ToString();
                year = year.Substring(year.Length - 2, 2);
                if (productNo.ContainsKey(dr["Productname"].ToString()))
                {
                    nexNo = productNo[dr["Productname"].ToString()] + 1;

                }
                else
                {
                    nexNo = GetNewContainerNextNo(dr["Productname"].ToString() + "/" + year);
                }
                dr["ContainerNo"] = year.ToString() + nexNo.ToString("D2");
                productNo[dr["Productname"].ToString()] = nexNo;

            }
            dt.AcceptChanges();
        }

        #endregion

        /// <summary>
        ///  获取产品信息 create tianFW 2017/09/12 用于选择产品
        /// </summary>
        /// <param name="strVaule"></param>
        /// <returns></returns>
        public DataTable  GetProductInfoLikeNameOrREVISION(string strVaule) {
            var strsql = new StringBuilder();
            strsql.AppendLine("SELECT  P.PRODUCTID,PB.PRODUCTNAME || ':' || P.PRODUCTREVISION|| '(' || PT.PRODUCTTYPENAME|| ')'  PRODUCTNAME,PB.REVOFRCDID");
            strsql.AppendLine("FROM PRODUCT P");
            strsql.AppendLine("LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = P.PRODUCTBASEID");
            strsql.AppendLine("LEFT JOIN PRODUCTTYPE PT ON PT.PRODUCTTYPEID = P.PRODUCTTYPEID");
            if (!string.IsNullOrWhiteSpace(strVaule))
            {
                strsql.AppendFormat(" WHERE (PB.PRODUCTNAME LIKE '%{0}%' OR PT.PRODUCTTYPENAME LIKE '%{1}%')", strVaule, strVaule);
            }
            return OracleHelper.Query(strsql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获取工艺信息，for 控件
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetWorkflowInfoByProductForAscx(Dictionary<string,string> para) {

            var sb = new StringBuilder();
            sb.AppendLine(@"SELECT P.PRODUCTID,WF.WORKFLOWID,WFB1.WORKFLOWBASEID,WFB1.WORKFLOWNAME || ':' || WF.WORKFLOWREVISION   WORKFLOWNAME
            ,wfb1.workflowname workflowname2,wf.workflowrevision");
            sb.AppendLine(" FROM PRODUCT P");
            sb.AppendLine("  LEFT JOIN PRODUCTBASE PB ON P.PRODUCTBASEID = PB.PRODUCTBASEID");
            sb.AppendLine("  LEFT JOIN WORKFLOWBASE WFB ON WFB.WORKFLOWBASEID = P.WORKFLOWBASEID");
            sb.AppendLine("  LEFT JOIN WORKFLOW WF ON WF.WORKFLOWID = NVL(WFB.REVOFRCDID, P.WORKFLOWID)");
            sb.AppendLine("  LEFT JOIN WORKFLOWBASE WFB1 ON WFB1.WORKFLOWBASEID = WF.WORKFLOWBASEID");
            sb.AppendLine(" where 1=1 ");


            if (!para.Keys.Contains("AllStatus")) {
                sb.AppendLine(" and wf.status=1 ");
            }

            if (para.Keys.Contains("ProductID") && !string.IsNullOrWhiteSpace(para["ProductID"]))
            {
                sb.AppendFormat(" AND p.ProductID ='{0}'", para["ProductID"]);
            }
            if (para.Keys.Contains("ProductName") && !string.IsNullOrWhiteSpace(para["ProductName"]))
            {
                sb.AppendFormat(" AND pb.ProductName ='{0}'", para["ProductName"]);
            }

            if (para.Keys.Contains("ProductRev") && !string.IsNullOrWhiteSpace(para["ProductRev"]))
            {
                sb.AppendFormat(" AND p.Productrevision ='{0}'", para["ProductRev"]);
            }


            if (para.Keys.Contains("WorkflowName") && !string.IsNullOrWhiteSpace(para["WorkflowName"]))
            {
                sb.AppendFormat(" AND WFB1.WORKFLOWNAME LIKE '%{0}%'", para["WorkflowName"]);
            }
                        

            DataTable dt= OracleHelper.Query(sb.ToString()).Tables[0];

            DataTable result = dt.Clone();

            DataRow[] drs = dt.Select("workflowid is not null");

            //查询件号所挂工艺的其他版本
            string strSql = @" select '{0}' PRODUCTID, w.workflowid,w.workflowbaseid,Wb.WORKFLOWNAME || ':' || W.WORKFLOWREVISION    WORKFLOWNAME,wb.workflowname  workflowname2,
 w.workflowrevision  from workflow w 
 left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid 
 where wb.workflowname='{1}' and w.workflowid<>'{2}'";

            if (!para.Keys.Contains("AllStatus"))
            {
                strSql += " and w.status=1 ";
            }

            foreach (DataRow dr in drs)
            {
                result.ImportRow(dr);

                string sql = string.Format(strSql,dr["PRODUCTID"].ToString(),dr["workflowname2"].ToString(), dr["WORKFLOWID"].ToString());

                DataTable tempDt = OracleHelper.Query(sql).Tables[0];
                if (tempDt.Rows.Count > 0)
                {
                    result.Merge(tempDt);
                }
            }



            return result ;

        }
        /// <summary>
        /// 获取工艺信息 for控件
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetWorkflowInfoForAscx(Dictionary<string,string> para) {
            string strSql = @"
 select w.workflowid,w.workflowrevision,wb.workflowname || ':' || w.workflowrevision  workflowname ,wb.workflowname workflowname2 from workflow w 
 left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid where 1=1 ";
            if (!para.Keys.Contains("AllStatus"))
            {
               strSql+=" and w.status=1 ";
            }
            if (para.Keys.Contains("WorkflowName")&&!String.IsNullOrWhiteSpace(para["WorkflowName"]))
                strSql += string.Format(" and wb.workflowname like '%{0}%' ",para["WorkflowName"]);

            return OracleHelper.Query(strSql).Tables[0];

        }

        /// <summary>
        /// 根据工艺获取工序信息
        /// </summary>
        /// <param name="workflowId"></param>
        /// <returns></returns>
        public DataTable GetStepInfoByWorkflowId(string workflowId)
        {
            string strSql = @"select w.workflowid,ws.sequence,ws.workflowstepname,ws.workflowstepid,s.specid,sb2.specname from workflow w 
left join workflowstep ws on ws.workflowid=w.workflowid
left join specbase sb on sb.specbaseid=ws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
left join specbase sb2 on sb2.specbaseid=s.specbaseid";
            
            strSql += string.Format(" where w.workflowid='{0}' ", workflowId);

            strSql += " order by ws.sequence asc ";

            return OracleHelper.Query(strSql).Tables[0];


        }

        /// <summary>
        /// 更新未任务指派的预派工数量,可能由于报废，拆批产生的数量变更
        /// add:Wangjh 20201124
        /// </summary>
        /// <param name="containerId"></param>
        public void UpdateTeamDispatchQty(string container) {
            string strSql = @"select c.qty currentQty,di.* from dispatchinfo di 
left join dispatchinfo di2 on di2.parentid=di.id
left join container c on c.containerid=di.containerid and c.status!=0
where  di.dispatchtype=0 and di2.id is null ";
            strSql +=string.Format(" and (c.containerid='{0}' or c.containername='{0}' )",container);

            DataTable dispatchDt = OracleHelper.Query(strSql).Tables[0];

            if (dispatchDt.Rows.Count == 0)
                return;

            int qty = Convert.ToInt32(dispatchDt.Rows[0]["currentQty"]);//数量

            ArrayList listSqls = new ArrayList();

            strSql = $"update dispatchinfo set qty={qty} ";

            foreach (DataRow dr in dispatchDt.Rows) {
                listSqls.Add(strSql + $" where id='{dr["id"].ToString()}'");
            }

            OracleHelper.ExecuteSqlTran(listSqls);
        }

        /// <summary>
        /// 获取应该变更的批次
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public uMESPagingDataDTO GetContainerChange(Dictionary<string, string> para, int intPageIndex, int intPageSize) {
            StringBuilder strQuery = new StringBuilder();
              strQuery.AppendLine(@"
select distinct wb.workflowname,w.workflowrevision,wb.workflowname || ':' || w.workflowrevision AS currentFlow,ws.sequence sequenceno,
m.processno,m.oprno,c.containername,pb.productname,p.description,p.productrevision,c.qty conqty,ws.workflowstepname SpecNameDisp,
c.containerid,c.productid,w.workflowid,s.specid,ws.sequence,ws.workflowstepid,

decode(p.productid,pb.revofrcdid,'','产品有新版') productchangeDesc,

CASE WHEN w.workflowid<>wb.revofrcdid THEN '工艺有新版' 
  
     WHEN a.workflowid<>w.workflowid THEN '含有临时工艺'

  END workflowchangeDesc
 from container c 

left join product p on p.productid=c.productid

left join productbase pb on pb.productbaseid=p.productbaseid

left join currentstatus cu on cu.currentstatusid=c.currentstatusid

left join workflowstep ws on ws.workflowstepid=cu.workflowstepid

left join workflow w on w.workflowid=ws.workflowid

left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid

left join mfgorder m on m.mfgorderid=c.mfgorderid

left join specbase sb on sb.specbaseid=ws.specbaseid

left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)

LEFT JOIN (


select sw.workflowid,swb.workflowname from workflow sw
left join workflowbase swb on swb.workflowbaseid=sw.workflowbaseid

WHERE sw.workflowrevision LIKE 'LS%' 

) a ON a.workflowname=wb.workflowname

where c.status!=0 and c.parentcontainerid is NULL ");

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(m.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(c.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND c.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND c.plannedcompletiondate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }

            strQuery.AppendLine(@" and  (p.productid<>pb.revofrcdid or wb.revofrcdid<>w.workflowid or a.workflowid<>w.workflowid )");

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strQuery.ToString(), intPageIndex, intPageSize);

            return retR;
        }

        /// <summary>
        /// 获取产品，不包含自己
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public DataTable GetProductWithOutSelf(string name,string rev) {
            string strSql = @"select p.productrevision,pb.productname,p.productid from product p
left join productbase pb on pb.productbaseid=p.productbaseid";

            strSql += $" where pb.productname='{name}' ";

            var dt= OracleHelper.Query(strSql).Tables[0];

            var temp = dt.Select("productrevision<>'" + rev + "'");

            if (temp.Length == 0)
                return new DataTable();
            else
               return temp.CopyToDataTable();
        }

        #region 获取需要打印的零件信息

        #endregion

        /// <summary>
        /// 根据工作令号查询所有产品
        /// </summary>
        /// <param name="processno"></param>
        /// <returns></returns>
        public DataTable GetProductByProcess(string processno) {
            string strSql = @"select P.PRODUCTID,PB2.PRODUCTNAME || ':' || P.PRODUCTREVISION|| '(' || PT.PRODUCTTYPENAME|| ')'  PRODUCTNAME,PB.REVOFRCDID from mfgorder m
left join productbase pb on pb.productbaseid=m.productbaseid
left join product p on p.productid=nvl(pb.revofrcdid,m.productid)
left join productbase pb2 on pb2.productbaseid=p.productbaseid
left join producttype pt on pt.producttypeid=p.producttypeid ";

            strSql += $" where m.processno like '%{processno}%' ";

            strSql += " group by pb2.productname,p.productid,p.productrevision,pb.revofrcdid,pt.producttypename ";
            strSql += " order by pb2.productname asc ";
            DataTable dt= OracleHelper.Query(strSql).Tables[0];

            foreach (DataRow row in dt.Rows) {
                if (row["PRODUCTID"] == row["REVOFRCDID"]) {
                    row["PRODUCTNAME"] += "(默认)";
                }
            }
            return dt;
        }

    }
}
